const genericPool = require("generic-pool");
const DbDriver = require("mysql");
const config = require("./config");
const log4 = require('../log4')
/**
 * Step 1 - Create pool using a factory object
 */
const factory = {
  create: function() {
    return DbDriver.createConnection({
      host     : config.host,
      user     : config.user,
      password : config.password,
      database : config.database
    });
  },
  destroy: function(client) {
    client.disconnect();
  }
};

const opts = {
  max: 10, // maximum size of the pool
  min: 2 // minimum size of the pool
};

const myPool = genericPool.createPool(factory, opts);
let Func = {
  /**
   * @function 执行sql语句
   * @param {String} sql 要执行的sql语句
   * @param {Array} params ?的值
   */
  query: (sql, params) => {
    let task = new Promise(function(resolve, reject) {
      myPool.acquire().then(function(client) {
        client.query(sql, params, function(err, result) {
          if (err) {
            log4.error(err)
            reject()
          }
          resolve(result)
          myPool.release(client);
        });
      })
    })
    return task;
  },
  /**
   * @function 获取所有数据
   * @param {String} table 搜索的表格
   * @param {Array} params 要获取的字段
   * @param {Object} query 条件对象
   */
  getAll: (table, params, query) => {
    let queryArr = []
    let queryValue = []
    for(let i in query) {
      if (query[i].toString().indexOf('?like') > -1) {
        queryArr.push(`${i} like ?`)
        queryValue.push(query[i].replace('?like', ''))
      } else {
        queryArr.push(`${i} = ?`)
        queryValue.push(query[i])
      }
    }
    let sql = `select ${params.join(',')} from ${table} where ${queryArr.join(' and ')}`
    let task = new Promise(function(resolve, reject) {
      myPool.acquire().then(function(client) {
        client.query(sql, queryValue, function(err, result) {
          if (err) {
            log4.error(err)
            reject()
          }
          // return object back to pool
          resolve(result)
          myPool.release(client);
        });
      });
    });
    return task;
  },
  /**
   * @function 获取第一条数据
   * @param {String} table 搜索的表格
   * @param {Array} params 要获取的字段
   * @param {Object} query 条件对象
   */
  getOne: (table, params, query) => {
    let queryArr = []
    let queryValue = []
    for(let i in query) {
      if (query[i].toString().indexOf('?like') > -1) {
        queryArr.push(`${i} like ?`)
        queryValue.push(query[i].replace('?like', ''))
      } else {
        queryArr.push(`${i} = ?`)
        queryValue.push(query[i])
      }
    }
    let sql = `select ${params.join(',')} from ${table} where ${queryArr.join(' and ')} limit 1`
    let task = new Promise(function(resolve, reject) {
      myPool.acquire().then(function(client) {
        client.query(sql, queryValue, function(err, result) {
          if (err) {
            log4.error(err)
            reject()
          }
          // return object back to pool
          resolve(result)
          myPool.release(client);
        });
      });
    });
    return task;
  },
  /**
   * @function 按分页获取
   * @param {String} table 搜索的表格
   * @param {Array} params 要获取的字段
   * @param {Object} query 条件对象
   * @param {Number} pageIndex 当前第几页
   * @param {Number} pageSize 每页的尺寸
   */
  getPage: (table, params, query, pageIndex = 1, pageSize = 10) => {
    let queryArr = []
    let queryValue = []
    for(let i in query) {
      if (query[i].toString().indexOf('?like') > -1) {
        queryArr.push(`${i} like ?`)
        queryValue.push(query[i].replace('?like', ''))
      } else {
        queryArr.push(`${i} = ?`)
        queryValue.push(query[i])
      }
    }
    let sql = `select ${params.join(',')} from ${table} where ${queryArr.join(' and ')} limit ${(pageIndex - 1) * pageSize}, ${pageSize}`
    let task = new Promise(function(resolve, reject) {
      myPool.acquire().then(function(client) {
        client.query(sql, queryValue, function(err, result) {
          if (err) {
            log4.error(err)
            reject()
          }
          // return object back to pool
          resolve(result)
          myPool.release(client);
        });
      });
    });
    return task;
  },
  /**
   * @function 更新数据
   * @param {String} table 更新的表格
   * @param {Object} params 要更新的值
   * @param {Object} query 条件对象
   */
  update: (table, params, query) => {
    // 处理更新的值
    let paramsArr = []
    let paramsValue = []
    for(let i in params) {
      paramsArr.push(`${i} = ?`)
      paramsValue.push(params[i])
    }
    // 处理条件
    let queryArr = []
    let queryValue = []
    for(let i in query) {
      if (query[i].toString().indexOf('?like') > -1) {
        queryArr.push(`${i} like ?`)
        queryValue.push(query[i].replace('?like', ''))
      } else {
        queryArr.push(`${i} = ?`)
        queryValue.push(query[i])
      }
    }
    let sql = `update ${table} set ${paramsArr.join(' , ')} where ${queryArr.join(' and ')}`
    let task = new Promise(function(resolve, reject) {
      myPool.acquire().then(function(client) {
        client.query(sql, [...paramsValue, ...queryValue], function(err, result) {
          if (err) {
            log4.error(err)
            reject()
          }
          // return object back to pool
          resolve(result)
          myPool.release(client);
        });
      });
    });
    return task;
  },
  /**
   * @function 插入数据
   * @param {String} table 插入的表格
   * @param {Object} params 要插入的值
   */
  insert: (table, params) => {
    // 处理更新的值
    let paramsArr = []
    let paramsValue = []
    for(let i in params) {
      paramsArr.push(`${i}`)
      paramsValue.push(`${params[i]}`)
    }
    let sql = `insert into ${table} (${paramsArr.join(' , ')}) VALUES (${[...'?'.repeat(paramsValue.length)].join(',')})`
    let task = new Promise(function(resolve, reject) {
      myPool.acquire().then(function(client) {
        client.query(sql, paramsValue, function(err, result) {
          if (err) {
            log4.error(err)
            reject()
          }
          // return object back to pool
          resolve(result)
          myPool.release(client);
        });
      });
    });
    return task;
  },
  /**
   * @function 根据ID获取相关的信息
   * @param {String} table 插入的表格
   * @param {Object} params 要查询的值
   * @param {String} id ID
   */
  getById: (table, params, id) => {
    return new Promise(function(resolve, reject) {
      this.getOne(table, params, {uid: id}).then((result) =>{
        resolve(result)
      }, (err) => {
        log4.error(err)
        reject()
      })
    });
  },
  /**
   * @function 插入数据
   * @param {String} table 插入的表格
   * @param {Array} paramsList 要插入的值
   */
  insertList (table, paramsList) {
    // 处理更新的值
    let sql_list = []
    let paramsValueList = []
    paramsList.map(params => {
      let paramsArr = []
      let paramsValue = []
      for(let i in params) {
        paramsArr.push(`${i}`)
        paramsValue.push(`${params[i]}`)
      }
      let sql = `insert into ${table} (${paramsArr.join(' , ')}) VALUES (${[...'?'.repeat(paramsValue.length)].join(',')})`
      sql_list.push(sql)
      paramsValueList.push(paramsValue)
    });
    let task = new Promise((resolve, reject) => {
      myPool.acquire().then((client) => {
          client.beginTransaction(err => {
            if (err) {
              log4.error(err)
              return false
            }
            let promiseList = []
            for(let i in sql_list) {
              let task = this.transactionQuery(sql_list[i], paramsValueList[i], client)
              promiseList.push(task)
            }
            Promise.all(promiseList).then(() => {
              client.commit(err => {
                if (err) {
                  client.rollback(function(err) {
                    myPool.release(client)
                    if (err) {
                      log4.error(err)
                      reject(`事务回滚失败`)
                    }
                    reject()
                  })
                }
                myPool.release(client)
                resolve()
              })
            }).catch((reason) => {
              client.rollback((err) => {
                myPool.release(client)
                if (err) {
                  log4.error(err)
                  reject(`事务回滚失败`)
                }
                reject()
              })
            })
          })
      });
    });
    return task;
  },
  /**
   * @function 给事务执行sql语句
   * @param {String} sql 要执行的sql语句
   * @param {Array} params ?的值
   * @param {Object} client 连接
   */
  transactionQuery (sql, params, client) {
    let task = new Promise(function(resolve, reject) {
      client.query(sql, params, function(err, result) {
        if (err) {
          log4.error(err)
          reject()
        }
        resolve(result)
      });
    });
    return task;
  },
  /**
   * @function 根据ID进行逻辑删除
   * @param {String} table 表名
   * @param {Array} idList ID列表
   */
  removeById (table, idList) {
    // 处理更新的值
    let sql = `update ${table} set enable = 0 where id in (${idList.join(',')})`
    let task = new Promise(function(resolve, reject) {
      myPool.acquire().then(function(client) {
        client.query(sql,function(err, result) {
          if (err) {
            log4.error(err)
            reject()
          }
          // return object back to pool
          resolve(result)
          myPool.release(client);
        });
      });
    });
    return task;
  }
}

// acquire connection - Promise is resolved
// once a resource becomes available
module.exports = Func;
